<?php

class CompaniesModel extends BaseModel {

	public function CompaniesModel() {
		parent::BaseModel();
		$this->setModel('company');
		$this->obj =& get_instance();
		$this->load->database();
	}

	public function retrieve_by_pkey($idField) {
		$rs = $this->db->query("SELECT C.*, I.industryname, U.firstname as account_owner_firstname, U.lastname as account_owner_lastname
								FROM company C LEFT JOIN industry I ON (C.industryid = I.id)
									LEFT JOIN users U ON (C.account_ownerid = U.id)
								WHERE C.id = $idField LIMIT 1")->result();

		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function retrieve_by_name($companyname) {
		$rs = $this->db->query("SELECT C.*, I.industryname, U.firstname as account_owner_firstname, U.lastname as account_owner_lastname
								FROM company C LEFT JOIN industry I ON (C.industryid = I.id)
									LEFT JOIN users U ON (C.account_ownerid = U.id)
								WHERE C.company_name = ".$this->db->escape($companyname)." LIMIT 1")->result();

		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function getMyCompany() {
		$rs = $this->db->query("SELECT C.*, I.industryname, U.firstname as account_owner_firstname, U.lastname as account_owner_lastname
								FROM company C LEFT JOIN industry I ON (C.industryid = I.id)
									LEFT JOIN users U ON (C.account_ownerid = U.id)
								WHERE C.yourcompany = 1 LIMIT 1")->result();

		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function getAssociatedContacts($companyid) {
		$sql = "SELECT C.*, U3.id as usersid
				FROM contact C INNER JOIN company_contact CC ON (C.id = CC.contactid AND CC.companyid = $companyid)
					LEFT JOIN users U3 ON (C.email1 = U3.emailaddress AND U3.deleted = 0)
				WHERE C.deleted = 0
				ORDER BY C.surname ASC";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getAssociatedJobs($companyid, $archived = 0, $job_status = 0) {
		$sql = "SELECT J.*, C2.firstname as primary_contact_firstname, C2.surname as primary_contact_surname
				FROM job J INNER JOIN company C ON (J.companyid = C.id AND J.companyid = $companyid)
					LEFT JOIN contact C2 ON (C2.id = J.contactid)
				WHERE 1 = 1 ";
					
		if ($archived) $sql .= " AND ( J.cancelled = 1 OR J.archived = 1) ";
		else $sql .= " AND J.archived = 0 AND J.cancelled = 0 ";
		
		if ($job_status) $sql .= " AND J.job_status = ".$this->db->escape($job_status)." ";
					
		$sql .=	"GROUP BY J.id
				ORDER BY J.job_title ASC";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getSuppliersJobs($companyid, $archived = 0, $job_status = 0) {
		$sql = "SELECT C.id as costid, C.cost_title, J.id, J.job_title, J.job_number, J.job_status, CO.id as companyid, CO.company_name
				FROM cost C INNER JOIN job J ON (C.jobid = J.id) INNER JOIN company CO ON (J.companyid = CO.id)
				WHERE C.supplierid = ".intval($companyid)."
					AND C.deleted = 0 ";
					
		if ($archived) $sql .= " AND ( J.cancelled = 1 OR J.archived = 1) ";
		else $sql .= " AND J.archived = 0 AND J.cancelled = 0 ";
		
		if ($job_status) $sql .= " AND J.job_status = ".$this->db->escape($job_status)." ";
					
		$sql .=	"GROUP BY J.id
				ORDER BY J.job_title ASC";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getCompanyOverview($clients = 0, $prospects = 0, $suppliers = 0, $archived = 0) {
		$sql = "SELECT C.*, count(J.id) as active_jobs, count(J2.id) as archived_jobs, U.firstname as account_owner_firstname, U.lastname as account_owner_lastname
				FROM company C LEFT JOIN job J ON (C.id = J.companyid AND J.archived = 0 AND J.cancelled = 0) 
					LEFT JOIN job J2 ON (C.id = J2.companyid AND J2.archived = 1 AND J2.cancelled = 0)
					LEFT JOIN users U ON (C.account_ownerid = U.id)
				WHERE 1 = 1 ";
		
		if ($clients) $sql .= " AND C.client = ".intval($clients);
		if ($prospects) $sql .= " AND C.prospect = ".intval($prospects);
		if ($suppliers) $sql .= " AND C.supplier = ".intval($suppliers);
		
		$sql .= " AND C.archived = ".intval($archived)."
				GROUP BY C.id
				ORDER BY C.company_name";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function quickSearch($searchterm, $archived = false, $companyid = false) {
		$returndata = null;
		
		$sql = "SELECT C.id as companyid, C.company_name, C.account_code, J.id as jobid, J.job_title, J.job_number 
				FROM company C INNER JOIN job J ON (C.id = J.companyid)
				WHERE 1 = 1 ";
				
				if (is_array($searchterm)) {
					foreach ($searchterm as $term) {
						$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.account_code LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.company_notes LIKE ".$this->db->escape('%'.$term.'%')."
									OR J.job_title LIKE ".$this->db->escape('%'.$term.'%')."
									OR J.job_number LIKE ".$this->db->escape('%'.$term.'%')."
									OR J.job_notes LIKE ".$this->db->escape('%'.$term.'%').")";
					}
				} else {
				
				$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.account_code LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.company_notes LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR J.job_title LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR J.job_number LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR J.job_notes LIKE ".$this->db->escape('%'.$searchterm.'%').") ";
					
				}
					
		if ($archived) $sql .= " AND J.archived = 1 OR J.cancelled = 1 ";
		else $sql .= " AND J.archived = 0 AND J.cancelled = 0 ";
		
		if ($companyid) $sql .= " AND C.id = ".intval($companyid);
		
		$rs = $this->db->query($sql)->result();
		
		$returndata->jobs = $rs;
		
		
		$sql = "SELECT C.id as companyid, C.company_name, C.account_code
				FROM company C
				WHERE C.client = 1 ";
				
				if (is_array($searchterm)) {
					foreach ($searchterm as $term) {
						$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.account_code LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.company_notes LIKE ".$this->db->escape('%'.$term.'%').")";
					}
				} else {
				
				$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.account_code LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.company_notes LIKE ".$this->db->escape('%'.$searchterm.'%').") ";
				}
					
		if ($archived) $sql .= " AND C.archived = 1 ";
		else $sql .= " AND C.archived = 0 ";
		
		if ($companyid) $sql .= " AND C.id = ".intval($companyid);
		
		$rs = $this->db->query($sql)->result();
		$returndata->clients = $rs;
		
		
		$sql = "SELECT C.id as companyid, C.company_name, C.account_code
				FROM company C
				WHERE C.prospect = 1 ";
				
				if (is_array($searchterm)) {
					foreach ($searchterm as $term) {
						$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.account_code LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.company_notes LIKE ".$this->db->escape('%'.$term.'%').")";
					}
				} else {
				
				$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.account_code LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.company_notes LIKE ".$this->db->escape('%'.$searchterm.'%').") ";
				}
					
		if ($archived) $sql .= " AND C.archived = 1 ";
		else $sql .= " AND C.archived = 0 ";
		
		if ($companyid) $sql .= " AND C.id = ".intval($companyid);
		
		$rs = $this->db->query($sql)->result();
		$returndata->prospects = $rs;
		
		$sql = "SELECT C.id as companyid, C.company_name, C.account_code
				FROM company C
				WHERE C.supplier = 1 ";
				
				if (is_array($searchterm)) {
					foreach ($searchterm as $term) {
						$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.account_code LIKE ".$this->db->escape('%'.$term.'%')."
									OR C.company_notes LIKE ".$this->db->escape('%'.$term.'%').")";
					}
				} else {
				
				$sql .=	" AND (C.company_name LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.account_code LIKE ".$this->db->escape('%'.$searchterm.'%')."
					OR C.company_notes LIKE ".$this->db->escape('%'.$searchterm.'%').") ";
				}
					
		if ($archived) $sql .= " AND C.archived = 1 ";
		else $sql .= " AND C.archived = 0 ";
		
		if ($companyid) $sql .= " AND C.id = ".intval($companyid);
		
		$rs = $this->db->query($sql)->result();
		$returndata->suppliers = $rs;
		
		return $returndata;
	
	}
	
	public function getAddresses($companyid, $address = 0) {
		$sql = "SELECT A.*, C.printable_name as country
				FROM address A LEFT JOIN country C ON (A.countryid = C.id)
				WHERE A.deleted = 0 ";
				
		$sql .= " AND A.companyid = ".intval($companyid);
				
		if ($address) $sql .= " AND A.id = ".intval($address);
				
		$rs = $this->db->query($sql)->result();
		
		if (count($rs) && $address) return $rs[0];
		else if (count($rs)) return $rs;
		else return false;
	}
	
	public function getCurrentCompanyForContact($contactid) {
		$sql = "SELECT C.*
				FROM company C INNER JOIN company_contact CC ON (C.id = CC.companyid)
				WHERE CC.contactid = ".intval($contactid)." 
					AND CC.current_company = 1
				LIMIT 1";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs[0];
		else return false;
	}
}

?>